package com.jeebey.mybatis.utils;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.binding.MapperMethod;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.jeebey.mybatis.annotation.Column;
import com.jeebey.mybatis.annotation.GeneratedPosition;
import com.jeebey.mybatis.annotation.GeneratedStrategy;
import com.jeebey.mybatis.annotation.GeneratedValue;
import com.jeebey.mybatis.annotation.Id;
import com.jeebey.mybatis.annotation.Table;
import com.jeebey.mybatis.annotation.Transient;
import com.jeebey.mybatis.exception.MybatisException;
import com.jeebey.mybatis.query.Criterion;
import com.jeebey.mybatis.query.Parameter;
import com.jeebey.mybatis.query.TypeReference;

/**
 * @since 1.0
 * @author <a href="mailto:84961426@qq.com">JuST4iT</a>
 * @version $Id: SqlGenerateUtil.java Feb 18, 2016 6:07:07 PM $
 */

public class SqlGenerateUtil {
	public static final Class<? extends Annotation> JPA_ID = Id.class;
	public static final Class<? extends Annotation> JPA_TABLE = Table.class;
	public static final Class<? extends Annotation> JPA_COLUMN = Column.class;
	public static final Class<? extends Annotation> JPA_TRANSIENT = Transient.class;
	public static final Class<? extends Annotation> JPA_GENERATED_VALUE = GeneratedValue.class;

	public static Map<String, String> sql_cache = Maps.newHashMap();

	private static List<Parameter> createParamsInsertMultiple(Class<?> clazz, List<?> list, Integer index) {
		List<Parameter> paramObjects = Lists.newArrayList();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			try {
				if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
					paramObjects
							.add(new Parameter("list[" + index + "]." + field.getName(), field.get(list.get(index))));
				}
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return paramObjects;
	}

	@Deprecated
	private static List<Parameter> createParamsSelectSingle(Class<?> clazz, List<?> list, Integer index) {
		List<Parameter> paramObjects = Lists.newArrayList();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			try {
				if (field.isAnnotationPresent(JPA_ID) && !field.isAnnotationPresent(JPA_TRANSIENT)) {
					paramObjects
							.add(new Parameter("list[" + index + "]." + field.getName(), field.get(list.get(index))));
				}
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return paramObjects;
	}

	private static List<Parameter> createParamsUpdateNotNul(Class<?> clazz, Object object, boolean bAccessible) {
		List<Parameter> paramObjects = Lists.newArrayList();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			try {
				if (field.get(object) != null && !field.isAnnotationPresent(JPA_TRANSIENT)) {
					paramObjects.add(new Parameter(field.getName(), field.get(object)));
				}
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return paramObjects;
	}

	@Deprecated
	public static List<Parameter> createParamsDeleteSingle(Class<?> clazz, Object object) {
		return createParamsSelectSingle(clazz, object);
	}

	public static List<Parameter> createParamsDeleteCondition(Class<?> clazz, Object criterion) {
		return createParamsSelectCondition(clazz, criterion);
	}

	public static List<Parameter> createParamsDeleteId(Class<?> clazz, Object id) {
		return createParamsSelectId(clazz, id);
	}

	public static List<Parameter> createParamsDeleteIds(Class<?> clazz, Object map) {
		return createParamsSelectIds(clazz, map);
	}

	@Deprecated
	public static List<Parameter> createParamsDeleteMultiple(Class<?> clazz, Object map) {
		return createParamsSelectMultiple(clazz, map);
	}

	public static List<Parameter> createParamsExcute(Object map) {
		List<Parameter> paramObjects = Lists.newArrayList();
		if (map != null) {
			List<?> listParams = (List<?>) ((Map<?, ?>) map).get("list");
			for (int i = 0; i < listParams.size(); i++) {
				paramObjects.add(new Parameter("list[" + i + "]", listParams.get(i)));
			}
		}
		return paramObjects;
	}

	public static List<Parameter> createParamsRepleceSingle(Class<?> clazz, Object object) {
		return createParamsInsertSingle(clazz, object, false);
	}

	public static List<Parameter> createParamsInsertSingle(Class<?> clazz, Object object, boolean bInsert) {
		List<Parameter> paramObjects = Lists.newArrayList();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			try {
				if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
					String _field = field.getName();
					Object _value = field.get(object);
					String keyProperty = null;
					String keyColumn = null;
					Class<?> javaType = null;
					String generator = null;
					boolean generated = false;
					GeneratedStrategy strategy = null;
					GeneratedPosition position = null;
					Class<?> referType = null;
					if (field.isAnnotationPresent(JPA_ID)) {
						if (field.isAnnotationPresent(JPA_COLUMN)) {
							Column column = (Column) field.getAnnotation(JPA_COLUMN);
							keyColumn = StringUtils.isEmpty(column.name()) ? field.getName() : column.name();
						}
						if (field.isAnnotationPresent(JPA_GENERATED_VALUE) && bInsert) {
							GeneratedValue generatedValue = (GeneratedValue) field.getAnnotation(JPA_GENERATED_VALUE);

							strategy = generatedValue.strategy();
							position = generatedValue.position();
							javaType = field.getType();
							generator = generatedValue.generator();
							referType = generatedValue.referType();
							generated = true;

							// 如果是uuid
							if (GeneratedStrategy.UUID.equals(strategy)) {
								field.set(object, StrCharUtil.uuid());
							}

							if (GeneratedStrategy.SHORTUUID.equals(strategy)) {
								field.set(object, StrCharUtil.shortuuid());
							}
						}
						keyProperty = _field;
					}
					paramObjects.add(new Parameter(_field, _value, keyColumn, keyProperty, strategy, generator,
							position, generated, javaType, referType));
				}
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return paramObjects;
	}

	public static List<Parameter> createParamsInsertNotNul(Class<?> clazz, Object object) {
		List<Parameter> paramObjects = Lists.newArrayList();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			try {
				if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
					String _field = field.getName();
					Object _value = field.get(object);
					String keyProperty = null;
					String keyColumn = null;
					Class<?> javaType = null;
					String generator = null;
					boolean generated = false;
					GeneratedStrategy strategy = null;
					GeneratedPosition position = null;
					Class<?> referType = null;
					if (field.isAnnotationPresent(JPA_ID)) {
						if (field.isAnnotationPresent(JPA_COLUMN)) {
							Column column = (Column) field.getAnnotation(JPA_COLUMN);
							keyColumn = StringUtils.isEmpty(column.name()) ? field.getName() : column.name();
						}
						if (field.isAnnotationPresent(JPA_GENERATED_VALUE)) {
							GeneratedValue generatedValue = (GeneratedValue) field.getAnnotation(JPA_GENERATED_VALUE);

							strategy = generatedValue.strategy();
							position = generatedValue.position();
							javaType = field.getType();
							generator = generatedValue.generator();
							referType = generatedValue.referType();
							generated = true;

							// 如果是uuid
							if (GeneratedStrategy.UUID.equals(strategy)) {
								field.set(object, StrCharUtil.uuid());
							}

							if (GeneratedStrategy.SHORTUUID.equals(strategy)) {
								field.set(object, StrCharUtil.shortuuid());
							}
						}
						keyProperty = _field;
					}
					if (_value != null) {
						paramObjects.add(new Parameter(_field, _value, keyColumn, keyProperty, strategy, generator,
								position, generated, javaType, referType));
					}
				}
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return paramObjects;
	}

	public static List<Parameter> createParamsInsertMultiple(Class<?> clazz, Object map) {
		List<Parameter> paramObjects = Lists.newArrayList();
		if (map != null) {
			List<?> listObject = (List<?>) ((Map<?, ?>) map).get("list");
			for (int i = 0; i < listObject.size(); i++) {
				paramObjects.addAll(createParamsInsertMultiple(clazz, listObject, i));
			}
		}
		return paramObjects;
	}

	@Deprecated
	public static List<Parameter> createParamsSelectSingle(Class<?> clazz, Object object) {
		List<Parameter> paramObjects = Lists.newArrayList();
		return paramObjects;
	}

	public static List<Parameter> createParamsSelectCondition(Class<?> clazz, Object criterion) {
		List<Parameter> paramObjects = Lists.newArrayList();
		if (criterion instanceof Criterion) {
			List<Object> params = ((Criterion) criterion).getParams();
			for (int i = 0; i < params.size(); i++) {
				paramObjects.add(new Parameter("params[" + i + "]", params.get(i)));
			}
		}
		return paramObjects;
	}

	public static List<Parameter> createParamsSelectCriterion(Object criterion) {
		return createParamsSelectCondition(null, criterion);
	}

	public static List<Parameter> createParamsSelectConditionCount(Class<?> clazz, Object criterion) {
		return createParamsSelectCondition(clazz, criterion);
	}

	public static List<Parameter> createParamsSelectConditionBounds(Class<?> clazz, Object map) {
		List<Parameter> paramObjects = Lists.newArrayList();
		if (map instanceof Map) {
			MapperMethod.ParamMap<?> parameterMap = (MapperMethod.ParamMap<?>) map;
			Criterion _criterion = ((Criterion) parameterMap.get("param1"));
			List<Object> params = _criterion == null ? Lists.newArrayList() : _criterion.getParams();
			for (int i = 0; i < params.size(); i++) {
				paramObjects.add(new Parameter("param1.params[" + i + "]", params.get(i)));
			}
		}
		return paramObjects;
	}

	public static List<Parameter> createParamsSelectConditionPaging(Class<?> clazz, Object map) {
		List<Parameter> paramObjects = Lists.newArrayList();
		if (map instanceof Map) {
			MapperMethod.ParamMap<?> parameterMap = (MapperMethod.ParamMap<?>) map;
			Criterion _criterion = ((Criterion) parameterMap.get("param1"));
			List<Object> params = _criterion == null ? Lists.newArrayList() : _criterion.getParams();
			for (int i = 0; i < params.size(); i++) {
				paramObjects.add(new Parameter("param1.params[" + i + "]", params.get(i)));
			}
		}
		return paramObjects;
	}

	public static List<Parameter> createParamsSelectCriterionPaging(Object map) {
		return createParamsSelectConditionPaging(null, map);
	}

	public static List<Parameter> createParamsSelectConditionRowbounds(Class<?> clazz, Object criterion) {
		return createParamsSelectCondition(clazz, criterion);
	}

	private static List<Parameter> createParamsSelectObjId(Class<?> clazz, Object object) {
		List<Parameter> paramObjects = Lists.newArrayList();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			try {
				if (field.isAnnotationPresent(JPA_ID) && !field.isAnnotationPresent(JPA_TRANSIENT)) {
					paramObjects.add(new Parameter(field.getName(), field.get(object)));
				}
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		return paramObjects;
	}

	public static List<Parameter> createParamsSelectAll() {
		return Lists.newArrayList();
	}

	public static List<Parameter> createParamsSelectAllCount() {
		return Lists.newArrayList();
	}

	public static List<Parameter> createParamsSelectId(Class<?> clazz, Object id) {
		List<Parameter> paramObjects = Lists.newArrayList();
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			try {
				if (field.isAnnotationPresent(JPA_ID) && !field.isAnnotationPresent(JPA_TRANSIENT)) {
					paramObjects.add(new Parameter(field.getName(), id));
				}
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			}
		}
		return paramObjects;
	}

	public static List<Parameter> createParamsSelectIds(Class<?> clazz, Object map) {
		List<Parameter> paramObjects = Lists.newArrayList();
		if (map != null) {
			List<?> listIds = (List<?>) ((Map<?, ?>) map).get("list");
			for (int i = 0; i < listIds.size(); i++) {
				paramObjects.add(new Parameter("list[" + i + "]", listIds.get(i)));
			}

		}
		return paramObjects;
	}

	@Deprecated
	public static List<Parameter> createParamsSelectMultiple(Class<?> clazz, Object map) {
		List<Parameter> paramObjects = Lists.newArrayList();
		if (map != null) {
			List<?> listObject = (List<?>) ((Map<?, ?>) map).get("list");
			for (int i = 0; i < listObject.size(); i++) {
				paramObjects.addAll(createParamsSelectSingle(clazz, listObject, i));
			}
		}
		return paramObjects;
	}

	public static List<Parameter> createParamsUpdateSingle(Class<?> clazz, Object object) {
		List<Parameter> paramObjects = Lists.newArrayList();
		paramObjects.addAll(createParamsInsertSingle(clazz, object, false));
		paramObjects.addAll(createParamsSelectObjId(clazz, object));
		return paramObjects;
	}

	public static List<Parameter> createParamsUpdateNotNul(Class<?> clazz, Object object) {
		List<Parameter> paramObjects = Lists.newArrayList();
		paramObjects.addAll(createParamsUpdateNotNul(clazz, object, true));
		paramObjects.addAll(createParamsSelectObjId(clazz, object));
		return paramObjects;
	}

	public static List<Parameter> createParamsUpdateCondition(Class<?> clazz, Object mapObject) {
		List<Parameter> paramObjects = Lists.newArrayList();
		MapperMethod.ParamMap<?> parameterMap = (MapperMethod.ParamMap<?>) mapObject;

		Object _paramsObj = parameterMap.get("param1");
		Criterion _criterion = (Criterion) parameterMap.get("param2");
		if (_paramsObj instanceof Object) {
			Field[] fields = clazz.getDeclaredFields();
			for (Field field : fields) {
				field.setAccessible(true);
				try {
					if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
						paramObjects.add(new Parameter("param1." + field.getName(), field.get(_paramsObj)));
					}
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}

		if (_criterion instanceof Criterion) {
			List<Object> params = ((Criterion) _criterion).getParams();
			for (int i = 0; i < params.size(); i++) {
				paramObjects.add(new Parameter("param2.params[" + i + "]", params.get(i)));
			}
		}

		return paramObjects;
	}

	public static List<Parameter> createParamsUpdateNotNulCondition(Class<?> clazz, Object mapObject) {
		List<Parameter> paramObjects = Lists.newArrayList();
		MapperMethod.ParamMap<?> parameterMap = (MapperMethod.ParamMap<?>) mapObject;
		Object _paramsObj = parameterMap.get("param1");
		Criterion _criterion = (Criterion) parameterMap.get("param2");
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			try {
				if (field.get(_paramsObj) != null && !field.isAnnotationPresent(JPA_TRANSIENT)) {
					paramObjects.add(new Parameter("param1." + field.getName(), field.get(_paramsObj)));
				}
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		if (_criterion instanceof Criterion) {
			List<Object> params = ((Criterion) _criterion).getParams();
			for (int i = 0; i < params.size(); i++) {
				paramObjects.add(new Parameter("param2.params[" + i + "]", params.get(i)));
			}
		}

		return paramObjects;
	}

	@Deprecated
	public static String createSqlDeleteSingle(Class<?> clazz) {
		String _sql_format = "DELETE FROM %s WHERE %s";
		String _table_name = sql_dbtable_name(clazz);
		String _where_select = sql_select_where_id(clazz);
		return String.format(_sql_format, _table_name, _where_select);
	}

	public static String createSqlDeleteCondition(Class<?> clazz, Object criterion) {
		String _sql_format = "DELETE FROM %s %s";
		String _table_name = sql_dbtable_name(clazz);
		String _where_select = "";
		if (criterion != null) {
			if (criterion instanceof Criterion) {
				_where_select = ((Criterion) criterion).toSQL();
			}
		}
		return String.format(_sql_format, _table_name, _where_select);
	}

	public static String createSqlDeleteId(Class<?> clazz) {
		String key_delete = clazz.getName() + ".createSqlDeleteId";
		String sql_delete = sql_cache.get(key_delete);
		if (sql_delete == null) {
			String _sql_format = "DELETE FROM %s WHERE %s";
			String _table_name = sql_dbtable_name(clazz);
			String _where_select = sql_select_where_id(clazz);
			sql_delete = String.format(_sql_format, _table_name, _where_select);
			sql_cache.put(key_delete, sql_delete);
		}
		return sql_delete;

	}

	public static String createSqlDeleteIds(Class<?> clazz, Object map) {
		String _sql_format = "DELETE FROM %s WHERE %s";
		String _table_name = sql_dbtable_name(clazz);
		String _where_delete = "";
		if (map != null) {
			List<?> list = (List<?>) ((Map<?, ?>) map).get("list");
			if (list != null && list.size() > 0) {
				String where_select = sql_select_where_id(clazz);
				for (int i = 0; i < list.size(); i++) {
					_where_delete += " or (" + where_select + ")";
				}
				_where_delete = _where_delete.substring(4);
			}
		}
		return String.format(_sql_format, _table_name, _where_delete);
	}

	@Deprecated
	public static String createSqlDeleteMultiple(Class<?> clazz, Object map) {
		String _sql_format = "DELETE FROM %s WHERE %s";
		String _table_name = sql_dbtable_name(clazz);
		String _where_delete = "";
		if (map != null) {
			List<?> list = (List<?>) ((Map<?, ?>) map).get("list");
			if (list != null && list.size() > 0) {
				String where_select = sql_select_where_id(clazz);
				for (int i = 0; i < list.size(); i++) {
					_where_delete += " or (" + where_select + ")";
				}
				_where_delete = _where_delete.substring(4);
			}
		}
		return String.format(_sql_format, _table_name, _where_delete);
	}

	public static String createSqlExcute(Object parameterObject) {
		if (parameterObject != null) {
			return (String) ((Map<?, ?>) parameterObject).get("sql");
		}
		return "error sql";
	}

	public static Object[] createSqlExcuteClass(Object parameterObject) {
		if (parameterObject != null) {
			Class<?> clazz = (Class<?>) ((Map<?, ?>) parameterObject).get("clazz");
			String sql = (String) ((Map<?, ?>) parameterObject).get("sql");
			return new Object[] { clazz, sql };
		}
		return new Object[] { Void.class, "error sql" };
	}

	public static Object[] createSqlExcuteReference(Object parameterObject) {
		if (parameterObject != null) {
			TypeReference<?> ref = (TypeReference<?>) ((Map<?, ?>) parameterObject).get("reference");
			String sql = (String) ((Map<?, ?>) parameterObject).get("sql");
			return new Object[] { ref.getType(), sql };
		}
		return new Object[] { Void.class, "error sql" };
	}

	public static String createSqlInsertSingle(Class<?> clazz) {
		String key_insert = clazz.getName() + ".createSqlInsertSingle";
		String sql_insert = sql_cache.get(key_insert);
		if (sql_insert == null) {
			String _sql_format = "INSERT INTO %s (%s) VALUES (%s)";
			String _table_name = sql_dbtable_name(clazz);
			String _filed_insert = sql_insert_filed(clazz);
			String _value_insert = sql_insert_value(clazz);
			sql_insert = String.format(_sql_format, _table_name, _filed_insert, _value_insert);
			sql_cache.put(key_insert, sql_insert);
		}
		return sql_insert;
	}

	public static String createSqlInsertNotNul(Class<?> clazz, Object object) {
		String key_insert = clazz.getName() + ".createSqlInsertNotNul";
		String sql_insert = sql_cache.get(key_insert);
		if (sql_insert == null) {
			String _sql_format = "INSERT INTO %s (%s) VALUES (%s)";
			String _table_name = sql_dbtable_name(clazz);
			String _filed_insert = sql_insert_filed(clazz, object);
			String _value_insert = sql_insert_value(clazz, object);
			sql_insert = String.format(_sql_format, _table_name, _filed_insert, _value_insert);
			sql_cache.put(key_insert, sql_insert);
		}
		return sql_insert;
	}

	public static String createSqlInsertMultiple(Class<?> clazz, Object listObject) {
		String _sql_format = "INSERT INTO %s (%s) VALUES (%s)";
		String _table_name = sql_dbtable_name(clazz);
		String _filed_insert = sql_insert_filed(clazz);
		String _value_insert = sql_insert_value(clazz);
		String _sql_insert = String.format(_sql_format, _table_name, _filed_insert, _value_insert);
		if (listObject != null) {
			List<?> list = (List<?>) ((Map<?, ?>) listObject).get("list");
			if (list != null && list.size() > 1) {
				for (int i = 0; i < list.size() - 1; i++) {
					_sql_insert += ",(" + _value_insert + ")";
				}
			}
		}

		return _sql_insert;
	}

	public static String createSqlReplaceSingle(Class<?> clazz) {
		String key_insert = clazz.getName() + ".createSqlReplaceSingle";
		String sql_insert = sql_cache.get(key_insert);
		if (sql_insert == null) {
			String _sql_format = "REPLACE INTO %s (%s) VALUES (%s)";
			String _table_name = sql_dbtable_name(clazz);
			String _filed_insert = sql_insert_filed(clazz);
			String _value_insert = sql_insert_value(clazz);
			sql_insert = String.format(_sql_format, _table_name, _filed_insert, _value_insert);
			sql_cache.put(key_insert, sql_insert);
		}
		return sql_insert;
	}

	@Deprecated
	public static String createSqlSelectSingle(Class<?> clazz) {
		String _sql_format = "SELECT %s FROM %s WHERE %s";
		String _table_name = sql_dbtable_name(clazz);
		String _filed_select = sql_select_filed(clazz);
		String _where_select = sql_select_where_id(clazz);
		return String.format(_sql_format, _filed_select, _table_name, _where_select);
	}

	public static String createSqlSelectCondition(Class<?> clazz, Object criterion) {
		String _sql_format = "SELECT %s FROM %s %s";
		String _filed_select = sql_select_filed(clazz);
		String _table_name = sql_dbtable_name(clazz);
		String _where_select = "";
		if (criterion != null) {
			if (criterion instanceof Criterion) {
				_where_select = ((Criterion) criterion).toSQL();
			}
		}
		return String.format(_sql_format, _filed_select, _table_name, _where_select);
	}

	public static String createSqlSelectCriterion(String sql, Object criterion) {
		String _sql_format = "%s %s";
		String _where_select = "";
		if (criterion != null) {
			if (criterion instanceof Criterion) {
				_where_select = ((Criterion) criterion).toSQL();
			}
		}
		return String.format(_sql_format, sql, _where_select);
	}

	public static String createSqlSelectConditionCount(Class<?> clazz, Object criterion) {
		String _sql_format = "SELECT COUNT(*) FROM %s %s";
		String _table_name = sql_dbtable_name(clazz);
		String _where_select = "";

		if (criterion != null) {
			if (criterion instanceof Criterion) {
				_where_select = ((Criterion) criterion).toSQL();
			}
		}
		return String.format(_sql_format, _table_name, _where_select);
	}

	public static String createSqlSelectConditionBounds(Class<?> clazz, Object mapObject) {
		String _sql_format = "SELECT %s FROM %s %s";
		String _filed_select = sql_select_filed(clazz);
		String _table_name = sql_dbtable_name(clazz);
		String _where_select = "";
		if (mapObject != null) {
			if (mapObject instanceof Map) {
				MapperMethod.ParamMap<?> parameterMap = (MapperMethod.ParamMap<?>) mapObject;
				Criterion _criterion = (Criterion) parameterMap.get("param1");
				_where_select = _criterion == null ? "" : _criterion.toSQL();
			}
		}
		return String.format(_sql_format, _filed_select, _table_name, _where_select);
	}

	public static String createSqlSelectConditionPaging(Class<?> clazz, Object mapObject) {
		String _sql_format = "SELECT %s FROM %s %s";
		String _filed_select = sql_select_filed(clazz);
		String _table_name = sql_dbtable_name(clazz);
		String _where_select = "";
		if (mapObject != null) {
			if (mapObject instanceof Map) {
				MapperMethod.ParamMap<?> parameterMap = (MapperMethod.ParamMap<?>) mapObject;
				Criterion _criterion = (Criterion) parameterMap.get("param1");
				_where_select = _criterion == null ? "" : _criterion.toSQL();
			}
		}
		return String.format(_sql_format, _filed_select, _table_name, _where_select);
	}

	public static String createSqlSelectCriterionPaging(String sql, Object mapObject) {
		String _sql_format = "%s %s";
		String _where_select = "";
		if (mapObject != null) {
			if (mapObject instanceof Map) {
				MapperMethod.ParamMap<?> parameterMap = (MapperMethod.ParamMap<?>) mapObject;
				Criterion _criterion = (Criterion) parameterMap.get("param1");
				_where_select = _criterion == null ? "" : _criterion.toSQL();
			}
		}
		return String.format(_sql_format, sql, _where_select);
	}

	public static String createSqlSelectCount(String sql) {
		String _sql_format = "SELECT COUNT(1) FROM (%s) _tbl_";
		String _sql = removeLastOrderBys(sql);
		return StringUtils.defaultString(optimizeSingleBys(_sql), String.format(_sql_format, optimizePagingBys(_sql)));
	}

	public static String createSqlSelectId(Class<?> clazz) {
		String key_select = clazz.getName() + ".createSqlSelectId";
		String sql_select = sql_cache.get(key_select);
		if (sql_select == null) {
			String _sql_format = "SELECT %s FROM %s WHERE %s";
			String _table_name = sql_dbtable_name(clazz);
			String _filed_select = sql_select_filed(clazz);
			String _where_select = sql_select_where_id(clazz);
			sql_select = String.format(_sql_format, _filed_select, _table_name, _where_select);
			sql_cache.put(key_select, sql_select);
		}
		return sql_select;
	}

	public static String createSqlSelectAll(Class<?> clazz) {
		String key_select = clazz.getName() + ".createSqlSelectAll";
		String sql_select = sql_cache.get(key_select);
		if (sql_select == null) {
			String _sql_format = "SELECT %s FROM %s";
			String _table_name = sql_dbtable_name(clazz);
			String _filed_select = sql_select_filed(clazz);
			sql_select = String.format(_sql_format, _filed_select, _table_name);
			sql_cache.put(key_select, sql_select);
		}
		return sql_select;
	}

	public static String createSqlSelectAllCount(Class<?> clazz) {
		String key_select = clazz.getName() + ".createSqlSelectAllCount";
		String sql_select = sql_cache.get(key_select);
		if (sql_select == null) {
			String _sql_format = "SELECT COUNT(*) FROM %s";
			String _table_name = sql_dbtable_name(clazz);
			sql_select = String.format(_sql_format, _table_name);
			sql_cache.put(key_select, sql_select);
		}
		return sql_select;
	}

	public static String createSqlSelectIds(Class<?> clazz, Object map) {
		String _sql_format = "SELECT %s FROM %s WHERE %s";
		String _table_name = sql_dbtable_name(clazz);
		String _filed_select = sql_select_filed(clazz);
		String _where_select = "";

		if (map != null) {
			List<?> list = (List<?>) ((Map<?, ?>) map).get("list");
			if (list != null && list.size() > 0) {
				String where_select = sql_select_where_id(clazz);
				for (int i = 0; i < list.size(); i++) {
					_where_select += " or (" + where_select + ")";
				}
				_where_select = _where_select.substring(4);
			}
		}
		return String.format(_sql_format, _filed_select, _table_name, _where_select);
	}

	@Deprecated
	public static String createSqlSelectMultiple(Class<?> clazz, Object map) {
		String _sql_format = "SELECT %s FROM %s WHERE %s";
		String _table_name = sql_dbtable_name(clazz);
		String _filed_select = sql_select_filed(clazz);
		String _where_select = "";

		if (map != null) {
			List<?> list = (List<?>) ((Map<?, ?>) map).get("list");
			if (list != null && list.size() > 0) {
				String where_select = sql_select_where_id(clazz);
				for (int i = 0; i < list.size(); i++) {
					_where_select += " or (" + where_select + ")";
				}
				_where_select = _where_select.substring(4);
			}
		}
		return String.format(_sql_format, _filed_select, _table_name, _where_select);

	}

	public static String createSqlUpdateSingle(Class<?> clazz) {
		String key_update = clazz.getName() + ".createSqlUpdateSingle";
		String sql_update = sql_cache.get(key_update);
		if (sql_update == null) {
			String _sql_format = "UPDATE %s SET %s WHERE %s";
			String _table_name = sql_dbtable_name(clazz);
			String _filed_update = sql_update_filed(clazz);
			String _where_select = sql_select_where_id(clazz);
			sql_update = String.format(_sql_format, _table_name, _filed_update, _where_select);
			sql_cache.put(key_update, sql_update);
		}
		return sql_update;

	}

	public static String createSqlUpdateNotNul(Class<?> clazz, Object clazzObject) {
		String _sql_format = "UPDATE %s SET %s WHERE %s";
		String _table_name = sql_dbtable_name(clazz);
		String _filed_update = sql_update_filed(clazz, clazzObject);
		String _where_select = sql_select_where_id(clazz);
		return String.format(_sql_format, _table_name, _filed_update, _where_select);
	}

	public static String createSqlUpdateCondition(Class<?> clazz, Object mapObject) {
		String _sql_format = "UPDATE %s SET %s %s";
		String _table_name = sql_dbtable_name(clazz);
		String _filed_update = sql_update_filed(clazz);
		String _where_select = "";
		if (mapObject != null) {
			if (mapObject instanceof Map) {
				MapperMethod.ParamMap<?> parameterMap = (MapperMethod.ParamMap<?>) mapObject;
				Criterion _criterion = (Criterion) parameterMap.get("param2");
				_where_select = _criterion == null ? "" : _criterion.toSQL();
			}
		}
		return String.format(_sql_format, _table_name, _filed_update, _where_select);
	}

	public static String createSqlUpdateNotNulCondition(Class<?> clazz, Object mapObject) {
		String _sql_format = "UPDATE %s SET %s %s";
		String _table_name = sql_dbtable_name(clazz);
		String _filed_update = "";
		String _where_select = "";
		if (mapObject != null) {
			if (mapObject instanceof Map) {
				MapperMethod.ParamMap<?> parameterMap = (MapperMethod.ParamMap<?>) mapObject;
				Criterion _criterion = (Criterion) parameterMap.get("param2");
				Object _clazzObj = parameterMap.get("param1");
				_filed_update = sql_update_filed(clazz, _clazzObj);
				_where_select = _criterion == null ? "" : _criterion.toSQL();
			}
		}
		return String.format(_sql_format, _table_name, _filed_update, _where_select);
	}

	public static String createSqlSelectFileds(Class<?> clazz, String props) {
		return sql_select_filed(clazz, Lists.newArrayList(StringUtils.replace(props, " ", "").split(",")));
	}

	private static String optimizeSingleBys(String sql) {
		String _sql = SqlExecutorUtil.removeBreakingWhitespace(sql);
		String _upd = _sql.toUpperCase();

		/** 特殊字段 GROUP */
		int i_g_pos = _upd.indexOf("GROUP BY");
		int i_h_pos = _upd.indexOf("HAVING");
		if (i_g_pos != -1 || i_h_pos != -1) {
			return null;
		}

		int i_s_pos = _upd.indexOf("SELECT");
		int i_f_pos = _upd.indexOf("FROM");
		int i_l_pos = _upd.indexOf("(");
		int i_r_pos = _upd.indexOf(")");

		if ((i_l_pos == -1 || i_r_pos == -1) || (i_l_pos > i_f_pos && i_r_pos > i_f_pos)) {
			String filed_str = _sql.substring(i_s_pos + 7, i_f_pos - 1);
			return StringUtils.replaceOnce(_sql, filed_str, "COUNT(1)");
		}

		return null;
	}

	private static String optimizePagingBys(String sql) {
		String _sql = SqlExecutorUtil.removeBreakingWhitespace(sql).trim();

		if (StrCharUtil.countSubStr2(_sql, "FROM") > 1) {
			return _sql;
		}

		String _upd = _sql.toUpperCase();
		int i_s_pos = _upd.indexOf("SELECT");
		int i_f_pos = _upd.indexOf("FROM");
		if (i_s_pos == -1 || i_f_pos == -1) {
			return _sql;
		}

		int i_l_pos = _upd.indexOf("(");
		int i_r_pos = _upd.indexOf(")");

		if ((i_l_pos == -1 || i_r_pos == -1) || (i_l_pos > i_f_pos && i_r_pos > i_f_pos)
				|| (i_l_pos != -1 && i_l_pos < i_f_pos && i_r_pos != -1 && i_r_pos < i_f_pos)) {
			String filed_str = _sql.substring(i_s_pos + 7, i_f_pos - 1);
			String[] fileds = filed_str.split(",");
			return StringUtils.replaceOnce(_sql, filed_str, fileds[0]);
		}
		return _sql;
	}

	private static String removeLastOrderBys(String sql) {
		String _sql = SqlExecutorUtil.removeBreakingWhitespace(sql);
		int iEnd = _sql.toUpperCase().lastIndexOf("ORDER BY");
		return iEnd == -1 ? _sql : _sql.substring(0, iEnd).trim();
	}

	private static String sql_insert_filed(Class<?> clazz) {
		StringBuilder filedBuilder = new StringBuilder("");
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
				if (field.isAnnotationPresent(JPA_COLUMN)) {
					Column column = (Column) field.getAnnotation(JPA_COLUMN);
					String _escape = column.escape() ? "`" : "";
					String _column = StringUtils.isEmpty(column.name()) ? field.getName() : column.name();
					filedBuilder.append(_escape).append(_column).append(_escape).append(",");
				} else {
					filedBuilder.append(field.getName()).append(",");
				}
			}
		}
		return filedBuilder.length() == 0 ? "" : filedBuilder.substring(0, filedBuilder.length() - 1);
	}

	private static String sql_insert_filed(Class<?> clazz, Object object) {
		StringBuilder filedBuilder = new StringBuilder("");
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
				try {
					Object value = field.get(object);
					if (value == null) {
						continue;
					}

					if (field.isAnnotationPresent(JPA_COLUMN)) {
						Column column = (Column) field.getAnnotation(JPA_COLUMN);
						String _escape = column.escape() ? "`" : "";
						String _column = StringUtils.isEmpty(column.name()) ? field.getName() : column.name();
						filedBuilder.append(_escape).append(_column).append(_escape).append(",");
					} else {
						filedBuilder.append(field.getName()).append(",");
					}
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		return filedBuilder.length() == 0 ? "" : filedBuilder.substring(0, filedBuilder.length() - 1);
	}

	private static String sql_insert_value(Class<?> clazz, Object object) {
		StringBuilder paramBuilder = new StringBuilder("");
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
				try {
					Object value = field.get(object);
					if (value == null) {
						continue;
					}

					paramBuilder.append("?,");
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		return paramBuilder.length() == 0 ? "" : paramBuilder.substring(0, paramBuilder.length() - 1);
	}

	private static String sql_insert_value(Class<?> clazz) {
		StringBuilder paramBuilder = new StringBuilder("");
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
				paramBuilder.append("?,");
			}
		}
		return paramBuilder.length() == 0 ? "" : paramBuilder.substring(0, paramBuilder.length() - 1);
	}

	private static String sql_select_filed(Class<?> clazz) {
		StringBuilder filedBuilder = new StringBuilder("");
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
				if (field.isAnnotationPresent(JPA_COLUMN)) {
					Column column = (Column) field.getAnnotation(JPA_COLUMN);
					String _escape = column.escape() ? "`" : "";
					String _column = StringUtils.isEmpty(column.name()) ? "" : _escape + column.name() + _escape;

					String _aliass = StringUtils.equalsIgnoreCase(column.name(), field.getName()) ? "" : (StringUtils
							.isEmpty(column.name()) ? "" : " ") + _escape + field.getName() + _escape;
					filedBuilder.append(_column).append(_aliass).append(",");
				} else {
					filedBuilder.append(field.getName() + ",");
				}
			}
		}
		return filedBuilder.length() == 0 ? "" : filedBuilder.substring(0, filedBuilder.length() - 1);
	}

	private static String sql_select_filed(Class<?> clazz, List<String> props) {
		StringBuilder filedBuilder = new StringBuilder("");
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			if (!field.isAnnotationPresent(JPA_TRANSIENT) && props.contains(field.getName())) {
				if (field.isAnnotationPresent(JPA_COLUMN)) {
					Column column = (Column) field.getAnnotation(JPA_COLUMN);
					String _escape = column.escape() ? "`" : "";
					String _column = StringUtils.isEmpty(column.name()) ? "" : _escape + column.name() + _escape;

					String _aliass = StringUtils.equalsIgnoreCase(column.name(), field.getName()) ? "" : (StringUtils
							.isEmpty(column.name()) ? "" : " ") + _escape + field.getName() + _escape;
					filedBuilder.append(_column).append(_aliass).append(",");
				} else {
					filedBuilder.append(field.getName() + ",");
				}
			}
		}
		return filedBuilder.length() == 0 ? "" : filedBuilder.substring(0, filedBuilder.length() - 1);
	}

	@SuppressWarnings("unused")
	@Deprecated
	private static String sql_select_where_single(Class<?> clazz, Object object, int type) {
		StringBuilder whereBulider = new StringBuilder("");
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			if (field.isAnnotationPresent(JPA_ID)) {
				if (field.isAnnotationPresent(JPA_COLUMN)) {
					Column column = (Column) field.getAnnotation(JPA_COLUMN);
					String _escape = column.escape() ? "`" : "";
					String _column = StringUtils.isEmpty(column.name()) ? field.getName() : column.name();
					whereBulider.append(_escape).append(_column).append(_escape).append("=?,");
				} else {
					whereBulider.append(field.getName()).append("=?,");
				}
			}
		}
		return whereBulider.length() == 0 ? "" : whereBulider.substring(0, whereBulider.length() - 1);
	}

	private static String sql_select_where_id(Class<?> clazz) {
		StringBuilder whereBulider = new StringBuilder("");
		Field[] fields = clazz.getDeclaredFields();
		boolean bExistIdFiled = false;
		for (Field field : fields) {
			field.setAccessible(true);
			if (field.isAnnotationPresent(JPA_ID)) {
				if (field.isAnnotationPresent(JPA_COLUMN)) {
					Column column = (Column) field.getAnnotation(JPA_COLUMN);
					String _escape = column.escape() ? "`" : "";
					String _column = StringUtils.isEmpty(column.name()) ? field.getName() : column.name();
					whereBulider.append(_escape).append(_column).append(_escape).append("=? AND ");
				} else {
					whereBulider.append(field.getName()).append("=? AND ");
				}
				bExistIdFiled = true;
			}
		}
		if (bExistIdFiled == false) {
			throw new MybatisException("在类 [ " + clazz.getName() + " ] 里没有相关字段配置 @Id ");
		}
		return whereBulider.length() == 0 ? "" : whereBulider.substring(0, whereBulider.length() - 4);
	}

	/**
	 * 获取实体类的表名
	 * 
	 * @param clazz
	 * @return
	 */
	private static String sql_dbtable_name(Class<?> clazz) {
		String key_table = clazz.getName() + ".sql_dbtable_name";
		String sql_table = sql_cache.get(key_table);
		if (sql_table == null) {
			if (!clazz.isAnnotationPresent(JPA_TABLE)) {
				sql_table = clazz.getSimpleName();
			} else {
				Table table = (Table) clazz.getAnnotation(JPA_TABLE);
				sql_table = StringUtils.isEmpty(table.name()) ? clazz.getSimpleName() : table.name();
			}
			sql_cache.put(key_table, sql_table);
		}
		return sql_table;
	}

	/**
	 * 更新所有字段
	 * 
	 * @param clazz
	 * @return
	 */
	private static String sql_update_filed(Class<?> clazz) {
		StringBuilder updateBuilder = new StringBuilder("");
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
				if (field.isAnnotationPresent(JPA_COLUMN)) {
					Column column = (Column) field.getAnnotation(JPA_COLUMN);
					String _escape = column.escape() ? "`" : "";
					String _column = StringUtils.isEmpty(column.name()) ? field.getName() : column.name();
					updateBuilder.append(_escape).append(_column).append(_escape).append("=?,");
				} else {
					updateBuilder.append(field.getName()).append("=?,");
				}
			}
		}
		return updateBuilder.length() == 0 ? "" : updateBuilder.substring(0, updateBuilder.length() - 1);
	}

	/**
	 * 更新非空字段
	 * 
	 * @param clazz
	 * @param object
	 * @return
	 */
	private static String sql_update_filed(Class<?> clazz, Object object) {
		StringBuilder updateBuilder = new StringBuilder("");
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			if (!field.isAnnotationPresent(JPA_TRANSIENT)) {
				try {
					Object value = field.get(object);
					if (value == null) {
						continue;
					}

					if (field.isAnnotationPresent(JPA_COLUMN)) {
						Column column = (Column) field.getAnnotation(JPA_COLUMN);
						String _escape = column.escape() ? "`" : "";
						String _column = StringUtils.isEmpty(column.name()) ? field.getName() : column.name();
						updateBuilder.append(_escape).append(_column).append(_escape).append("=?,");
					} else {
						updateBuilder.append(field.getName()).append("=?,");
					}
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		return updateBuilder.length() == 0 ? "" : updateBuilder.substring(0, updateBuilder.length() - 1);
	}

}
